use std::sync::atomic::Ordering;

use crate::error::{new_err, new_err_msg, AppErr, SqlxErr, SERVICE_EXISTS_DEVICE, SERVICE_NO_DEVICE};
use crate::server::device::get_conn;
use futures_util::TryFutureExt;
use serde::{Deserialize, Serialize};
use sqlx::Row;
use sqlx::{Executor, SqliteConnection};

use super::get_pool;

const CREATE_SQL: &'static str = r#"
CREATE TABLE IF NOT EXISTS tb_device (
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    name TEXT NOT NULL, 
    mac_addr TEXT NOT NULL, 
    location TEXT NOT NULL, 
    currency_code TEXT NOT NULL, 
    lang TEXT NOT NULL, 
    money INTEGER NOT NULL, 
    UNIQUE(mac_addr)
)
"#;

const UNKNOWN: &'static str = "unknown";

pub const CURRENCY_CNY: &str = "CNY";
pub const CURRENCY_EUR: &str = "EUR";

pub const LANG_ZH: &'static str = "zh-CN";
pub const LANG_ES: &'static str = "es"; // 西班牙
pub const LANG_FR: &'static str = "fr"; // 法语
pub const LANG_IT: &'static str = "it"; // 意大利

pub const DEF_MONEY: u32 = 100; // 2位小数

#[derive(Debug, Serialize, Deserialize)]
pub struct Device {
    pub id: i64,
    pub name: String,
    pub mac_addr: String,
    pub location: String,
    pub currency_code: String,
    pub lang: String,
    pub money: u32,
}

pub async fn init() -> Result<(), SqlxErr> {
    get_pool().execute(CREATE_SQL).await?;

    Ok(())
}

async fn is_exists(conn: &mut SqliteConnection, mac_addr: &str) -> Result<bool, SqlxErr> {
    let row = sqlx::query(r#"SELECT id FROM tb_device WHERE mac_addr = ? LIMIT 1"#)
        .bind(mac_addr)
        .fetch_optional(conn)
        .await?;
    Ok(row.is_some())
}

async fn create_impl(
    conn: &mut SqliteConnection,
    name: &str,
    mac_addr: &str,
    location: &str,
    currency_code: &str,
    lang: &str,
    money: u32,
) -> Result<i64, SqlxErr> {
    let ret = sqlx::query(r#"
        INSERT INTO tb_device (name, mac_addr, location, currency_code, lang, money) VALUES (?, ?, ?, ?, ?, ?)
    "#)
    .bind(name)
    .bind(mac_addr)
    .bind(location)
    .bind(currency_code)
    .bind(lang)
    .bind(money)
    .execute(conn)
    .await?;

    Ok(ret.last_insert_rowid())
}

async fn get_by_mac_addr(conn: &mut SqliteConnection, mac_addr: &str) -> Result<Device, AppErr> {
    let row = sqlx::query(
        r#"
        SELECT id, name, mac_addr, location, currency_code, lang, money FROM tb_device WHERE mac_addr = ? LIMIT 1
    "#,
    )
    .bind(mac_addr)
    .fetch_optional(conn)
    .await?
    .ok_or(new_err("没有找到该设备"))?;

    let device = Device {
        id: row.get(0),
        name: row.get(1),
        mac_addr: row.get(2),
        location: row.get(3),
        currency_code: row.get(4),
        lang: row.get(5),
        money: row.get(6),
    };

    Ok(device)
}

pub async fn login(mac_addr: &str) -> Result<Device, AppErr> {
    let mut tx = get_pool().begin().await?;

    if !is_exists(&mut tx, mac_addr).await? {
        create_impl(
            &mut tx,
            UNKNOWN,
            mac_addr,
            UNKNOWN,
            CURRENCY_CNY,
            LANG_ZH,
            DEF_MONEY,
        )
        .await?;
    }
    let device = get_by_mac_addr(&mut *tx, mac_addr).await?;

    tx.commit().await?;

    Ok(device)
}

pub async fn create(
    name: &str,
    mac_addr: &str,
    location: &str,
    currency_code: &str,
    lang: &str,
    money: u32
) -> Result<i64, AppErr> {

    let mut tx = get_pool().begin().await?;

    if is_exists(&mut *tx, mac_addr).await? {
        return Err(SERVICE_EXISTS_DEVICE.into_err());
    }

    let id = create_impl(&mut *tx, name, mac_addr, location, currency_code, lang, money).await?;

    tx.commit().await?;

    Ok(id)
}

pub async fn get_device(id: i64) -> Result<Device, AppErr> {
    let row = sqlx::query(r#"SELECT id, name, mac_addr, location, currency_code, lang, money FROM tb_device WHERE id = ? LIMIT 1"#)
        .bind(id)
        .fetch_optional( get_pool() )
        .await?
        .ok_or( SERVICE_NO_DEVICE.into_err() )?;
    let device = Device {
        id: row.get(0),
        name: row.get(1),
        mac_addr: row.get(2),
        location: row.get(3),
        currency_code: row.get(4),
        lang: row.get(5),
        money: row.get(6),
    };
    Ok(device)
}

pub async fn edit(device: &Device) -> Result<(), AppErr> {
    let mut tx = get_pool().begin().await?;

    if is_exists(&mut *tx, &device.mac_addr).await? {
        let dev = get_by_mac_addr(&mut *tx, &device.mac_addr).await?;
        return Err(new_err_msg(format!("MAC地址与{}重复", dev.name)));
    }

    sqlx::query(r#"
        UPDATE tb_device SET name = ?, mac_addr = ?, location = ?, currency_code = ?, lang = ?, money = ? WHERE id = ?
    "#)
    .bind(&device.name)
    .bind(&device.mac_addr)
    .bind(&device.location)
    .bind(&device.currency_code)
    .bind(&device.lang)
    .bind(device.money)
    .bind(device.id)
    .execute(&mut *tx)
    .await?;

    tx.commit().await?;

    Ok(())
}

pub async fn del(id: i64) -> Result<(), SqlxErr> {
    sqlx::query(r#"DELETE FROM tb_device WHERE id = ?"#)
        .bind(id)
        .execute(get_pool())
        .await?;
    Ok(())
}

#[derive(Debug, Serialize)]
pub struct OnlineInfo {
    connect_timestamp: u64,
    ping_count: u32,
    addr: String,
}

#[derive(Debug, Serialize)]
pub struct OnlineDeviceInfo {
    device: Device,
    online_info: Option<OnlineInfo>,
}

pub async fn query() -> Result<Box<[OnlineDeviceInfo]>, SqlxErr> {
    let rows = sqlx::query(
        r#"SELECT id, name, mac_addr, location, currency_code, lang, money FROM tb_device"#,
    )
    .fetch_all(get_pool())
    .await?;

    let mut vec = Vec::with_capacity(rows.len());
    for row in rows {
        let device = Device {
            id: row.get(0),
            name: row.get(1),
            mac_addr: row.get(2),
            location: row.get(3),
            currency_code: row.get(4),
            lang: row.get(5),
            money: row.get(6),
        };
        let conn = get_conn(device.id);
        let info = if let Some(conn) = conn {
            Some(OnlineInfo {
                connect_timestamp: conn.connect_timestamp,
                ping_count: conn.ping_count.load(Ordering::Relaxed),
                addr: conn.addr.to_string(),
            })
        } else {
            None
        };
        vec.push(OnlineDeviceInfo {
            device,
            online_info: info,
        })
    }
    Ok(vec.into_boxed_slice())
}
